import json
import openpyxl

# 读取JSON文件
with open('C:\\Users\\DELL\\Desktop\\work\\1.json', 'r', encoding='utf-8') as json_file:
    data = json.load(json_file)

# 读取xlsx文件
xlsx_data = openpyxl.load_workbook('C:\\Users\\DELL\\Desktop\\work\\1.xlsx')
ws = xlsx_data.active

# 获取第一列和第二列的数据
column1 = [cell.value for cell in ws['A']]
column2 = [cell.value for cell in ws['B']]

# 将xlsx文件的第一列作为字典的键，第二列作为字典的值
replace_dict = dict(zip(column1, column2))


# 遍历JSON数据，搜索并替换特定内容
def search_and_replace(obj):
    if isinstance(obj, dict):
        for key, value in obj.items():
            if isinstance(value, (dict, list)):
                search_and_replace(value)
            elif value in replace_dict:
                obj[key] = replace_dict[value]
    elif isinstance(obj, list):
        for i in range(len(obj)):
            if isinstance(obj[i], (dict, list)):
                search_and_replace(obj[i])
            elif obj[i] in replace_dict:
                obj[i] = replace_dict[obj[i]]


search_and_replace(data)

# 保存修改后的JSON文件
with open('C:\\Users\\DELL\\Desktop\\work\\new_1.json', 'w', encoding='utf-8') as json_file:
    json.dump(data, json_file, ensure_ascii=False, indent=4)

print("执行完成!")
